﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace ADOTestingLibrary
{
    public class CRUDClass
    {
        public List<Product> Select()
        {
            List<Product> products = new List<Product>();

            SqlConnection cn = initConnection();

            try
            {
                cn.Open();
                string query = "SELECT * FROM Products";
                SqlCommand cm = new SqlCommand(query, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                
                Product p;
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    p = new Product();
                    p.ProductId = dr.GetInt32(0);
                    p.ProductName = dr.GetString(1);
                    p.Price = dr.GetDecimal(2);
                    products.Add(p);
                }
            }
            catch (Exception ex)
            {
                cn.Close();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                cn.Close();
            }
            return products;
        }

        public List<Product> Select1()
        {
            List<Product> products = new List<Product>();

            SqlConnection conn = initConnection();

            try
            {
                conn.Open();

                string query = "SELECT * FROM Product WHERE Price<300 AND ProductName LIKE 'B%'";

                SqlCommand command = new SqlCommand(query, conn);

                SqlDataReader reader = command.ExecuteReader();

                Product p;

                while (reader.Read())
                {
                    p = new Product();
                    p.ProductId = Convert.ToInt32(reader[0]);
                    p.ProductName = Convert.ToString(reader[1]);
                    p.Price = Convert.ToDecimal(reader[2]);

                    products.Add(p);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return products;
        }

        public Product Select2()
        {
            Product product = new Product();

            SqlConnection conn = initConnection();

            try
            {
                conn.Open();

                string query = "SELECT * FROM Product WHERE ProductId=3";

                SqlCommand command = new SqlCommand(query, conn);

                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    product.ProductId = Convert.ToInt32(reader[0]);
                    product.ProductName = Convert.ToString(reader[1]);
                    product.Price = Convert.ToDecimal(reader[2]);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return product;
        }

        public Product Update1()
        {
            SqlConnection conn = initConnection();
            SqlTransaction transaction = null;
            Product p = new Product();

            try
            {
                conn.Open();

                transaction = conn.BeginTransaction();

                string query = "UPDATE Product SET ProductName='Updated',Price=99999 WHERE ProductId=10";
                SqlCommand command = new SqlCommand(query, conn);

                command.Transaction = transaction;
                command.ExecuteNonQuery();

                transaction.Commit();

                query = "SELECT * FROM Product WHERE ProductId=10";

                command = new SqlCommand(query, conn);
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    p.ProductId = Convert.ToInt32(reader[0]);
                    p.ProductName = Convert.ToString(reader[1]);
                    p.Price = Convert.ToDecimal(reader[2]);
                }

            }
            catch (Exception ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                conn.Close();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return p;
        }

        public bool Update2()
        {
            SqlConnection conn = initConnection();
            SqlTransaction transaction = null;
            bool result = false;

            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();

                string query = "UPDATE Product SET ProductName='Updated',Price=55555 WHERE ProductName LIKE 'B%'";

                SqlCommand command = new SqlCommand(query, conn);

                command.Transaction = transaction;
                result = command.ExecuteNonQuery() > 0;

                transaction.Commit();
            }
            catch (Exception ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                conn.Close();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }

            return result;
        }

        public void Delete()
        {
            SqlConnection conn = initConnection();
            SqlTransaction transaction = null;

            try
            {
                conn.Open();
                transaction = conn.BeginTransaction();

                string query = "DELETE FROM Product WHERE ProductId=13";

                SqlCommand command = new SqlCommand(query, conn);

                command.Transaction = transaction;
                command.ExecuteNonQuery();

                transaction.Commit();
            }
            catch (Exception ex)
            {
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                conn.Close();
                Console.WriteLine(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

        public SqlConnection initConnection()
        {
            //string connectionString = "Data Source=PUDY-PC;Initial Catalog=Q12013Db;Integrated Security=True";
            string connectionString = "Data Source=MTPC309\\SQLEXPRESS;Initial Catalog=Q12013Database;Integrated Security=True";
            SqlConnection conn = new SqlConnection(connectionString);

            return conn;
        }
    }
}
